// NewtonRaphsonIRRCalculator.cs - Calculate the Internal rate of return for a given set of cashflows.
// Zainco Ltd
//
// See http://zainco.blogspot.com/2008/08/internal-rate-of-return-using-newton.html for background context
//
// Author: Joseph A. Nyirenda <joseph.nyirenda@gmail.com>
//             Mai Kalange<code5p@yahoo.co.uk>
// Copyright (c) 2008 Joseph A. Nyirenda, Mai Kalange, Zainco Ltd
//
// This program is free software; you can redistribute it and/or
// modify it under the terms of version 2 of the GNU General Public
// License as published by the Free Software Foundation.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
// General Public License for more details.
//
// You should have received a copy of the GNU General Public
// License along with this program; if not, write to the
// Free Software Foundation, Inc., 59 Temple Place - Suite 330,
// Boston, MA 02111-1307, USA.


// SQL Usage:
//  DECLARE @Revenues AS NVARCHAR(MAX) = '-3000, 1850, 1400, 1000'
//  SELECT dbo.SqlIRR(@Revenues)


using System;
using System.Data.SqlTypes;

namespace SqlCLR.Functions
{
    
    // ReSharper disable once InconsistentNaming
    // ReSharper disable once UnusedMember.Global
    public class ExcelFunction
    {
        public const double tol = 0.001;
        public delegate double fx(double x);
        public static fx composeFunctions(fx f1, fx f2)
        {
            return (double x) => f1(x) + f2(x);
        }

        public static fx f_xirr(double p, double dt, double dt0)
        {
            return (double x) => p * Math.Pow((1.0 + x), ((dt0 - dt) / 365.0));
        }

        public static fx df_xirr(double p, double dt, double dt0)
        {
            return (double x) => (1.0 / 365.0) * (dt0 - dt) * p * Math.Pow((x + 1.0), (((dt0 - dt) / 365.0) - 1.0));
        }

        public static fx total_f_xirr(double[] payments, double[] days)
        {
            fx resf = (double x) => 0.0;

            for (int i = 0; i < payments.Length; i++)
            {
                resf = composeFunctions(resf, f_xirr(payments[i], days[i], days[0]));
            }

            return resf;
        }

        public static fx total_df_xirr(double[] payments, double[] days)
        {
            fx resf = (double x) => 0.0;

            for (int i = 0; i < payments.Length; i++)
            {
                resf = composeFunctions(resf, df_xirr(payments[i], days[i], days[0]));
            }

            return resf;
        }

        public static double Newtons_method(double guess, fx f, fx df)
        {
            double x0 = guess;
            double x1 = 0.0;
            double err = 1e+100;

            while (err > tol)
            {
                x1 = x0 - f(x0) / df(x0);
                err = Math.Abs(x1 - x0);
                x0 = x1;
            }

            return x0;
        }
        /// <summary>
        /// Takes a string of values and returns the Internal rate of return.
        /// </summary>
        /// <param name="values">a NVARCHAR(MAX) string of numeric values in the form Internal rate of return.</param>
        /// <returns>A value between 0 and 1 indicating the IRR of the financial series passed in.</returns>

        [Microsoft.SqlServer.Server.SqlFunction]

        // ReSharper disable once InconsistentNaming
        // ReSharper disable once UnusedMember.Global
        public static SqlDouble IRR(string values)
        {
            Array.ConvertAll(values.Split(','), double.Parse);
            double[] cashFlows = Array.ConvertAll(values.Split(','), double.Parse);
            ICalculator calculator = new NewtonRaphsonIrrCalculator(cashFlows);

            return calculator.Execute();

        }

        [Microsoft.SqlServer.Server.SqlFunction]

        // ReSharper disable once InconsistentNaming
        // ReSharper disable once UnusedMember.Global
        public static SqlDouble XIRR(string values_amount,string values_date)
        {
            Array.ConvertAll(values_amount.Split(','), double.Parse);
            Array.ConvertAll(values_date.Split(','), double.Parse);
            double[] payments = Array.ConvertAll(values_amount.Split(','), double.Parse);
            double[] days = Array.ConvertAll(values_date.Split(','), double.Parse);


            return Newtons_method(0.1,
                                         total_f_xirr(payments, days),
                                         total_df_xirr(payments, days));        

        }
    }

    public class NewtonRaphsonIrrCalculator : ICalculator
    {
        private readonly double[] _cashFlows;
        private int _numberOfIterations;
        private double _result;

        public NewtonRaphsonIrrCalculator(double[] cashFlows)
        {
            _cashFlows = cashFlows;
        }

        /// <summary>
        /// Gets a value indicating whether this instance is valid cash flows.
        /// </summary>
        /// <value>
        /// 	<c>true</c> if this instance is valid cash flows; otherwise, <c>false</c>.
        /// </value>
        private bool IsValidCashFlows
        {
            //Cash flows for the first period must be negative
            //There should be at least two cash flow periods         
            get
            {
                const int minNoCashFlowPeriods = 2;

                if (_cashFlows.Length < minNoCashFlowPeriods || (_cashFlows[0] > 0))
                {
                    throw new ArgumentOutOfRangeException(
                        $"Cash flow for the first period  must be negative and there should be at least two periods");
                }
                return true;
            }
        }

        /// <summary>
        /// Gets the initial guess.
        /// </summary>
        /// <value>The initial guess.</value>
        private double InitialGuess
        {
            get
            {
                double initialGuess = -1 * (1 + (_cashFlows[1] / _cashFlows[0]));
                return initialGuess;
            }
        }

        #region ICalculator Members

        public double Execute()
        {
            if (IsValidCashFlows)
            {
                DoNewtonRapshonCalculation(InitialGuess);

                if (_result > 1)
                    throw new IrrCalculationException(
                        "Failed to calculate the IRR for the cash flow series. Please provide a valid cash flow sequence");
            }
            return _result;
        }

        #endregion

        /// <summary>
        /// Does the newton rapshon calculation.
        /// </summary>
        /// <param name="estimatedReturn">The estimated return.</param>
        /// <returns></returns>
        private void DoNewtonRapshonCalculation(double estimatedReturn)
        {
            _numberOfIterations++;
            _result = estimatedReturn - SumOfIrrPolynomial(estimatedReturn) / IrrDerivativeSum(estimatedReturn);
            while (!HasConverged(_result) && ConfigurationHelper.MaxIterations != _numberOfIterations)
            {
                DoNewtonRapshonCalculation(_result);
            }
        }


        /// <summary>
        /// Sums the of IRR polynomial.
        /// </summary>
        /// <param name="estimatedReturnRate">The estimated return rate.</param>
        /// <returns></returns>
        private double SumOfIrrPolynomial(double estimatedReturnRate)
        {
            double sumOfPolynomial = 0;
            if (IsValidIterationBounds(estimatedReturnRate))
                for (int j = 0; j < _cashFlows.Length; j++)
                {
                    sumOfPolynomial += _cashFlows[j] / (Math.Pow((1 + estimatedReturnRate), j));
                }
            return sumOfPolynomial;
        }

        /// <summary>
        /// Determines whether the specified estimated return rate has converged.
        /// </summary>
        /// <param name="estimatedReturnRate">The estimated return rate.</param>
        /// <returns>
        /// 	<c>true</c> if the specified estimated return rate has converged; otherwise, <c>false</c>.
        /// </returns>
        private bool HasConverged(double estimatedReturnRate)
        {
            //Check that the calculated value makes the IRR polynomial zero.
            bool isWithinTolerance = Math.Abs(SumOfIrrPolynomial(estimatedReturnRate)) <= ConfigurationHelper.Tolerance;
            return (isWithinTolerance);
        }

        /// <summary>
        /// IRRs the derivative sum.
        /// </summary>
        /// <param name="estimatedReturnRate">The estimated return rate.</param>
        /// <returns></returns>
        private double IrrDerivativeSum(double estimatedReturnRate)
        {
            double sumOfDerivative = 0;
            if (IsValidIterationBounds(estimatedReturnRate))
                for (int i = 1; i < _cashFlows.Length; i++)
                {
                    sumOfDerivative += _cashFlows[i] * (i) / Math.Pow((1 + estimatedReturnRate), i);
                }
            return sumOfDerivative * -1;
        }

        /// <summary>
        /// Determines whether [is valid iteration bounds] [the specified estimated return rate].
        /// </summary>
        /// <param name="estimatedReturnRate">The estimated return rate.</param>
        /// <returns>
        /// 	<c>true</c> if [is valid iteration bounds] [the specified estimated return rate]; otherwise, <c>false</c>.
        /// </returns>
        private static bool IsValidIterationBounds(double estimatedReturnRate)
        {
            // ReSharper disable once CompareOfFloatsByEqualityOperator
            return estimatedReturnRate != -1 && (estimatedReturnRate < int.MaxValue) &&
                   (estimatedReturnRate > int.MinValue);
        }
    }

    public static class ConfigurationHelper
    {
        public static readonly int MaxIterations = 50000;

        public static readonly double Tolerance = 0.00000001;
    }

    public class IrrCalculationException : Exception
    {
        public IrrCalculationException(string message) : base(message)
        {
        }
    }
    public interface ICalculator
    {
        /// <summary>
        /// Executes this instance.
        /// </summary>
        /// <returns></returns>
        double Execute();
    }
}